package org.csu.mypetstore.persistence.Impl;

import org.csu.mypetstore.domain.Item;
import org.csu.mypetstore.domain.Product;
import org.csu.mypetstore.persistence.DBUtil;
import org.csu.mypetstore.persistence.ItemDAO;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
//对Item表的操作
public class ItemDAOImpl implements ItemDAO {
    //sql语句
    private static final String GET_ITEM_LIST_BY_PRODUCT = "SELECT I.ITEMID,LISTPRICE,UNITCOST,SUPPLIER AS supplierId,I.PRODUCTID AS productId,NAME AS productName,DESCN AS productDescription,CATEGORY AS categoryId,STATUS,ATTR1 AS attribute1,ATTR2 AS attribute2,ATTR3 AS attribute3,ATTR4 AS attribute4,ATTR5 AS attribute5 FROM ITEM I, PRODUCT P WHERE P.PRODUCTID = I.PRODUCTID AND I.PRODUCTID = ?";
    private static final String GET_ITEM = "select I.ITEMID,LISTPRICE,UNITCOST,SUPPLIER AS supplierId,I.PRODUCTID AS productId,NAME AS productName,DESCN AS productDescription,CATEGORY AS CategoryId,STATUS,ATTR1 AS attribute1,ATTR2 AS attribute2,ATTR3 AS attribute3,ATTR4 AS attribute4,ATTR5 AS attribute5,QTY AS quantity from ITEM I, INVENTORY V, PRODUCT P where P.PRODUCTID = I.PRODUCTID and I.ITEMID = V.ITEMID and I.ITEMID=?";
    private static final String UPDATE_INVENTORY_QUANTITY = "UPDATE INVENTORY SET QTY = QTY - ? WHERE ITEMID = ?";
    private static final String GET_INVENTORY_QUANTITY = "SELECT QTY AS QUANTITY FROM INVENTORY WHERE ITEMID = ?";


    //通过product的id来获得其所有item
    @Override
    public List<Item> getItemListByProduct(String productId) {
        List<Item> itemList = new ArrayList<Item>();
        try {
            //连接
            Connection connection = DBUtil.getConnection();
            //preparedStatement对象
            PreparedStatement preparedStatement = connection.prepareStatement(GET_ITEM_LIST_BY_PRODUCT);
            //占位符赋值
            preparedStatement.setString(1, productId);
            //结果集
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                //获取这个item
                Item item = new Item();
                item.setItemId(resultSet.getString(1));
                item.setListPrice(resultSet.getBigDecimal(2));
                item.setUnitCost(resultSet.getBigDecimal(3));
                item.setSupplierId(resultSet.getInt(4));
                //获取这个product
                Product product = new Product();
                product.setProductId(resultSet.getString(5));
                product.setName(resultSet.getString(6));
                product.setDescription(resultSet.getString(7));
                product.setCategoryId(resultSet.getString(8));
                //配对
                item.setProduct(product);
                //item
                item.setStatus(resultSet.getString(9));
                item.setAttribute1(resultSet.getString(10));
                item.setAttribute2(resultSet.getString(11));
                item.setAttribute3(resultSet.getString(12));
                item.setAttribute4(resultSet.getString(13));
                item.setAttribute5(resultSet.getString(14));
                itemList.add(item);
            }
            //关闭连接
            DBUtil.closeResultSet(resultSet);
            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeResultSet(resultSet);
        } catch (Exception e) {
            e.printStackTrace();
        }

        return itemList;
    }

    //通过itemId获得该item
    @Override
    public Item getItem(String itemId) {
        Item item = null;
        try {
            //连接
            Connection connection = DBUtil.getConnection();
            //Prepared statement对象
            PreparedStatement preparedStatement = connection.prepareStatement(GET_ITEM);
            //占位符赋值
            preparedStatement.setString(1, itemId);
            //结果集
            ResultSet resultSet = preparedStatement.executeQuery();

            if (resultSet.next()) {
                //item
                item = new Item();
                item.setItemId(resultSet.getString(1));
                item.setListPrice(resultSet.getBigDecimal(2));
                item.setUnitCost(resultSet.getBigDecimal(3));
                item.setSupplierId(resultSet.getInt(4));
                //product
                Product product = new Product();
                product.setProductId(resultSet.getString(5));
                product.setName(resultSet.getString(6));
                product.setDescription(resultSet.getString(7));
                product.setCategoryId(resultSet.getString(8));
                //绑定
                item.setProduct(product);
                //item
                item.setStatus(resultSet.getString(9));
                item.setAttribute1(resultSet.getString(10));
                item.setAttribute2(resultSet.getString(11));
                item.setAttribute3(resultSet.getString(12));
                item.setAttribute4(resultSet.getString(13));
                item.setAttribute5(resultSet.getString(14));
                item.setQuantity(resultSet.getInt(15));
            }
            //关闭连接
            DBUtil.closeResultSet(resultSet);
            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return item;
    }

    //更新库存数量
    @Override
    public void updateInventoryQuantity(Map<String, Object> param) {
        try {
            //获得连接
            Connection connection = DBUtil.getConnection();
            //preparedStatement语句
            PreparedStatement preparedStatement = connection.prepareStatement(UPDATE_INVENTORY_QUANTITY);
            //要更新的项的id
            String itemId = param.keySet().iterator().next();
            //获取要更新的数量
            Integer increment = Integer.parseInt(param.get(itemId).toString());
            //Integer increment = (Integer) param.get(itemId);
            //占位符赋值
            preparedStatement.setInt(1, increment.intValue());
            preparedStatement.setString(2,itemId);
            //执行sql语句
            preparedStatement.executeUpdate();
            //关闭连接
            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //获得库存数量
    @Override
    public int getInventoryQuantity(String itemId) {
        int result = -1;
        try {
            //获得连接
            Connection connection = DBUtil.getConnection();
            //preparedStatement语句
            PreparedStatement preparedStatement = connection.prepareStatement(GET_INVENTORY_QUANTITY);
            //占位符赋值
            preparedStatement.setString(1, itemId);
            //执行sql语句获得结果集
            ResultSet resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                result = resultSet.getInt(1);
            }
            //关闭连接
            DBUtil.closeResultSet(resultSet);
            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

}
